use chrono::Utc;
use sqlx::{SqlitePool, FromRow};
use crate::error::AppResult;
use crate::models::{Plan, CreatePlan};

/// 计划和用户信息
#[derive(Debug, Clone, FromRow)]
pub struct PlanWithUser {
    pub plan_id: i64,
    pub user_id: i64,
    pub user_name: String,
    pub plan_name: String,
    pub description: String,
    pub status: String,
    pub frequency: String,
    pub next_reminder: chrono::DateTime<Utc>,
    pub last_sent_at: Option<chrono::DateTime<Utc>>,
}

/// 创建新计划
pub async fn create(pool: &SqlitePool, create_plan: &CreatePlan) -> AppResult<Plan> {
    let now = Utc::now();
    
    let result = sqlx::query(
        r#"INSERT INTO plan (user_id, name, description, created_at, updated_at, status, frequency, next_reminder) 
           VALUES (?, ?, ?, ?, ?, 'pending', ?, ?)"#
    )
    .bind(create_plan.user_id)
    .bind(&create_plan.name)
    .bind(&create_plan.description)
    .bind(now)
    .bind(now)
    .bind(&create_plan.frequency)
    .bind(create_plan.next_reminder)
    .execute(pool)
    .await?;
    
    let plan_id = result.last_insert_rowid();
    
    let plan = find_by_id(pool, plan_id).await?
        .expect("Failed to find newly created plan");
    
    Ok(plan)
}

/// 根据 ID 查找计划
pub async fn find_by_id(pool: &SqlitePool, id: i64) -> AppResult<Option<Plan>> {
    let plan = sqlx::query_as::<_, Plan>(
        "SELECT * FROM plan WHERE id = ?"
    )
    .bind(id)
    .fetch_optional(pool)
    .await?;
    
    Ok(plan)
}

/// 查找所有待提醒的计划（包含用户名）
/// 逻辑：
/// 1. 查找 next_reminder 在当前时间前5分钟以内的计划
/// 2. 且 last_sent_at 为空或距离 next_reminder 超过1分钟（防止重复发送）
/// 这样可以确保即使 scheduler 稍有延迟也不会错过提醒
pub async fn find_pending_reminders_with_user(pool: &SqlitePool) -> AppResult<Vec<PlanWithUser>> {
    let now = Utc::now();
    // 提前5分钟开始检测，确保不会错过
    let check_from = now + chrono::Duration::minutes(5);
    
    let plans = sqlx::query_as::<_, PlanWithUser>(
        r#"SELECT 
               p.id as plan_id,
               p.user_id,
               u.name as user_name,
               p.name as plan_name,
               p.description,
               p.status,
               p.frequency,
               p.next_reminder,
               p.last_sent_at
           FROM plan p
           INNER JOIN user u ON p.user_id = u.id
           WHERE p.status = 'pending' 
           AND p.next_reminder <= ? 
           AND (p.last_sent_at IS NULL OR abs(strftime('%s', p.next_reminder) - strftime('%s', p.last_sent_at)) > 60)
           ORDER BY p.next_reminder"#
    )
    .bind(check_from)
    .fetch_all(pool)
    .await?;
    
    Ok(plans)
}

/// 查找所有待提醒的计划
/// 逻辑：
/// 1. 查找 next_reminder 在当前时间前5分钟以内的计划
/// 2. 且 last_sent_at 为空或距离 next_reminder 超过1分钟（防止重复发送）
/// 这样可以确保即使 scheduler 稍有延迟也不会错过提醒
pub async fn find_pending_reminders(pool: &SqlitePool) -> AppResult<Vec<Plan>> {
    let now = Utc::now();
    // 提前5分钟开始检测，确保不会错过
    let check_from = now + chrono::Duration::minutes(5);
    
    let plans = sqlx::query_as::<_, Plan>(
        r#"SELECT * FROM plan 
           WHERE status = 'pending' 
           AND next_reminder <= ? 
           AND (last_sent_at IS NULL OR abs(strftime('%s', next_reminder) - strftime('%s', last_sent_at)) > 60)
           ORDER BY next_reminder"#
    )
    .bind(check_from)
    .fetch_all(pool)
    .await?;
    
    Ok(plans)
}

/// 更新计划状态
pub async fn update_status(pool: &SqlitePool, id: i64, status: &str) -> AppResult<()> {
    let now = Utc::now();
    
    sqlx::query(
        "UPDATE plan SET status = ?, updated_at = ? WHERE id = ?"
    )
    .bind(status)
    .bind(now)
    .bind(id)
    .execute(pool)
    .await?;
    
    Ok(())
}

/// 更新下次提醒时间
pub async fn update_next_reminder(pool: &SqlitePool, id: i64, next_reminder: chrono::DateTime<Utc>) -> AppResult<()> {
    let now = Utc::now();
    
    sqlx::query(
        "UPDATE plan SET next_reminder = ?, updated_at = ? WHERE id = ?"
    )
    .bind(next_reminder)
    .bind(now)
    .bind(id)
    .execute(pool)
    .await?;
    
    Ok(())
}

/// 更新最后发送时间（防止重复发送）
pub async fn update_last_sent(pool: &SqlitePool, id: i64) -> AppResult<()> {
    let now = Utc::now();
    
    sqlx::query(
        "UPDATE plan SET last_sent_at = ?, updated_at = ? WHERE id = ?"
    )
    .bind(now)
    .bind(now)
    .bind(id)
    .execute(pool)
    .await?;
    
    Ok(())
}

/// 查找用户的所有计划
pub async fn find_by_user_id(pool: &SqlitePool, user_id: i64) -> AppResult<Vec<Plan>> {
    let plans = sqlx::query_as::<_, Plan>(
        "SELECT * FROM plan WHERE user_id = ? ORDER BY created_at DESC"
    )
    .bind(user_id)
    .fetch_all(pool)
    .await?;
    
    Ok(plans)
}
